﻿Public Class FrmRechModifSuppClient

#Region "Champs"
    Dim n, p, pre, nom, email, rue, vil, cp, telpor, telfixe, rencomp As String
    Dim i, idcli, idc As Integer
    Dim position As Integer
    Dim macnx As New PPE3.ClassMySqlConnect("853339_depanneur", "071064")
    Dim nbchamps As Integer = 0
    Dim donnees As New Data.DataTable
#End Region

    Private Sub FrmRechercheClient_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.MdiParent = FrmStart
        Me.Top = 0
        'SeConnecter()
        'MaRequete = "SELECT idClient as NumeroClient, nomClient,PrenomClient,AdresseClient, CodePostalClient, VilleClient,TelPortClient,TelFixeClient,EmailClient,RenseignementCompAdresse FROM client"
        'MaCommandeSpecialRequete.CommandText = MaRequete
        'MonRemplisseur.SelectCommand = MaCommandeSpecialRequete
        'MonRemplisseur.Fill(MonJeuDeDonnees, "TousLesClients")
        'MaConnexion.Close()
        BtnModif.BackColor = Color.Red
        LblinfoModif.ForeColor = Color.Red
    End Sub

#Region "Méthode"

    Public Sub Clear()
        TextBoxIdClient.Text = ""
        TextBoxNomClient.Text = ""
        TextBoxPrenomClient.Text = ""
        TextBoxAdresse.Text = ""
        TextBoxCP.Text = ""
        TextBoxVille.Text = ""
        TextBoxTelPort.Text = ""
        TextBoxTelFixe.Text = ""
        TextBoxEmail.Text = ""
        TextBoxRenComp.Text = ""
    End Sub
    Public Sub Rafraichir()
        Try

            TextBoxIdClient.Text = macnx.GetChamp_DataTable(donnees, position, 0)
            TextBoxNomClient.Text = RemplaceSlashApostrophes(macnx.GetChamp_DataTable(donnees, position, 1))
            TextBoxPrenomClient.Text = macnx.GetChamp_DataTable(donnees, position, 2)
            TextBoxAdresse.Text = RemplaceSlashApostrophes(macnx.GetChamp_DataTable(donnees, position, 3))
            TextBoxCP.Text = macnx.GetChamp_DataTable(donnees, position, 4)
            TextBoxVille.Text = RemplaceSlashApostrophes(macnx.GetChamp_DataTable(donnees, position, 5))
            TextBoxTelPort.Text = macnx.GetChamp_DataTable(donnees, position, 6)
            TextBoxTelFixe.Text = macnx.GetChamp_DataTable(donnees, position, 7)
            TextBoxEmail.Text = macnx.GetChamp_DataTable(donnees, position, 8)
            TextBoxRenComp.Text = RemplaceSlashApostrophes(macnx.GetChamp_DataTable(donnees, position, 9))
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Private Sub RechargerLesDonnees()
        macnx.Se_Connecter_MySqlServer()
        macnx.Vider_Dataset()
        macnx.Se_Deconnecter_MySqlServer()
    End Sub
    Private Sub RechargerLesDonneesSupp()
        macnx.Se_Connecter_MySqlServer()
        macnx.Vider_Dataset()
        TextBoxIdClient.Text = ""
        TextBoxNomClient.Text = ""
        TextBoxPrenomClient.Text = ""
        TextBoxAdresse.Text = ""
        TextBoxCP.Text = ""
        TextBoxVille.Text = ""
        TextBoxTelPort.Text = ""
        TextBoxTelFixe.Text = ""
        TextBoxEmail.Text = ""
        TextBoxRenComp.Text = ""
        macnx.Se_Deconnecter_MySqlServer()
    End Sub

#End Region

#Region "Méthode rechercher"
    Private Sub TextBoxNom_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBoxNom.TextChanged
        If TextBoxNom.Text <> "" Then

            Try
                n = TextBoxNom.Text
            Catch ex As InvalidCastException
                Exit Sub
            End Try
            macnx.Se_Connecter_MySqlServer()
            macnx.viderVue("idClient_as_NumeroClient")
            DataGridViewRecherche.DataSource = RemplaceSlashApostrophesDataTable(macnx.Executer_Requete_Select("SELECT idClient as NumeroClient, nomClient,PrenomClient,AdresseClient, CodePostalClient, VilleClient,TelPortClient,TelFixeClient,EmailClient,RenseignementCompAdresse  FROM Client Where NomClient like '" & "%" & n & "%" & "' ", "idClient_as_NumeroClient"))
            DataGridViewRecherche.AutoResizeColumns()
            macnx.viderVue("idClient_as_NumeroClient2")
            donnees = macnx.Executer_Requete_Select("SELECT idClient as NumeroClient, nomClient,PrenomClient,AdresseClient, CodePostalClient, VilleClient,TelPortClient,TelFixeClient,EmailClient,RenseignementCompAdresse  FROM Client Where NomClient like '" & "%" & n & "%" & "' ", "idClient_as_NumeroClient2")
            nbchamps = donnees.Rows.Count - 1
            Rafraichir()
            macnx.Se_Deconnecter_MySqlServer()
        End If
    End Sub

    Private Sub TextBoxPrenom_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBoxPrenom.TextChanged
        If TextBoxPrenom.Text <> "" Then

            Try
                p = TextBoxPrenom.Text
            Catch ex As InvalidCastException
                Exit Sub
            End Try
            macnx.Se_Connecter_MySqlServer()
            macnx.viderVue("idClient")
            DataGridViewRecherche.DataSource = RemplaceSlashApostrophesDataTable(macnx.Executer_Requete_Select("SELECT idClient as NumeroClient, nomClient,PrenomClient,AdresseClient, CodePostalClient, VilleClient,TelPortClient,TelFixeClient,EmailClient,RenseignementCompAdresse  FROM Client Where PrenomClient like '" & "%" & p & "%" & "' ", "idClient"))
            DataGridViewRecherche.AutoResizeColumns()
            macnx.viderVue("idClient2")
            donnees = macnx.Executer_Requete_Select("SELECT idClient as NumeroClient, nomClient,PrenomClient,AdresseClient, CodePostalClient, VilleClient,TelPortClient,TelFixeClient,EmailClient,RenseignementCompAdresse  FROM Client Where PrenomClient like '" & "%" & p & "%" & "' ", "idClient2")
            nbchamps = donnees.Rows.Count - 1
            Rafraichir()
            macnx.Se_Deconnecter_MySqlServer()
        End If
    End Sub

    Private Sub TextBoxId_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBoxId.TextChanged
        If TextBoxId.Text <> "" Then

            Try
                i = TextBoxId.Text
            Catch ex As InvalidCastException
                Exit Sub
            End Try
            macnx.Se_Connecter_MySqlServer()
            macnx.viderVue("prenomCli")
            DataGridViewRecherche.DataSource = RemplaceSlashApostrophesDataTable(macnx.Executer_Requete_Select("SELECT idClient as NumeroClient, nomClient,PrenomClient,AdresseClient, CodePostalClient, VilleClient,TelPortClient,TelFixeClient,EmailClient,RenseignementCompAdresse  FROM Client Where idClient like '" & "%" & i & "%" & "' ", "prenomCli"))
            DataGridViewRecherche.AutoResizeColumns()
            macnx.viderVue("prenomCli2")
            donnees = macnx.Executer_Requete_Select("SELECT idClient as NumeroClient, nomClient,PrenomClient,AdresseClient, CodePostalClient, VilleClient,TelPortClient,TelFixeClient,EmailClient,RenseignementCompAdresse  FROM Client Where idClient like '" & "%" & i & "%" & "' ", "prenomCli2")
            nbchamps = donnees.Rows.Count - 1
            Rafraichir()
            macnx.Se_Deconnecter_MySqlServer()
        End If
    End Sub
#End Region

#Region "Méthode bouton modif et supp "

    Private Sub BtnModif_Click(sender As System.Object, e As System.EventArgs) Handles BtnModif.Click
        Try
            If BtnModif.Text = "Modif" Then
                BtnModif.Text = "Valider!"
                BtnModif.BackColor = Color.Green
                LblinfoModif.ForeColor = Color.Green
                LblinfoModif.Text = "Après avoir modifié, cliquez sur Valider! --->"

            Else
                If TextBoxNomClient.Text <> "" Then
                    idc = CInt(TextBoxIdClient.Text)
                    nom = RemplaceApostrophesSlash(TextBoxNomClient.Text)
                    pre = TextBoxPrenomClient.Text
                    email = TextBoxEmail.Text
                    rue = TextBoxAdresse.Text
                    vil = TextBoxVille.Text
                    cp = TextBoxCP.Text
                    telpor = TextBoxTelPort.Text
                    telfixe = TextBoxTelFixe.Text
                    rencomp = RemplaceApostrophesSlash(TextBoxRenComp.Text)

                    macnx.Se_Connecter_MySqlServer()
                    macnx.Executer_Requete_Action("update Client Set NomClient = '" & nom & "', PrenomClient ='" & pre & "',  AdresseClient ='" & rue & "', CodePostalClient ='" & cp & "', VilleClient ='" & vil & "', TelPortClient = '" & telpor & "', TelFixeClient = '" & telfixe & "',  EmailClient = '" & email & "', RenseignementCompAdresse = '" & rencomp & "' where IdClient=" & idc & "")
                    macnx.Se_Deconnecter_MySqlServer()
                    MessageBox.Show("Information du Client Modifié...!", "Alerte!", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    position = 0
                    RechargerLesDonnees()
                    BtnModif.Text = "Modif"
                    'Rafraichir()
                    BtnModif.BackColor = Color.Red
                    LblinfoModif.ForeColor = Color.Red
                    LblinfoModif.Text = "Avant toute modification , cliquez sur Modif --->"
                    Clear()
                    TextBoxNom.Clear()
                    TextBoxPrenom.Clear()
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub BtnSupp_Click(sender As System.Object, e As System.EventArgs) Handles BtnSupp.Click
        Try

            If TextBoxNomClient.Text <> "" Then

                idc = CInt(TextBoxIdClient.Text)
                macnx.Se_Connecter_MySqlServer()
                macnx.Executer_Requete_Action("Delete from date Where idCli = " & idc & ";Delete from Intervention Where idCli = " & idc & "; Delete from client Where IdClient = " & idc & "; ")
                MessageBox.Show("Client Supprimé...!", "Alerte!", MessageBoxButtons.OK, MessageBoxIcon.Information)
                macnx.Se_Deconnecter_MySqlServer()
                RechargerLesDonneesSupp()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

#End Region

    Private Sub DataGridViewRecherche_Click(sender As Object, e As System.EventArgs) Handles DataGridViewRecherche.Click
        Dim id As Object = 0

        Me.TextBoxIdCli.Text = Me.DataGridViewRecherche.SelectedCells.Item(index:=id).Value.ToString

    End Sub

    Private Sub TextBoxIdCli_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBoxIdCli.TextChanged
        Try
            idcli = TextBoxIdCli.Text
        Catch ex As InvalidCastException
            Exit Sub
        End Try
        macnx.Se_Connecter_MySqlServer()
        macnx.viderVue("modifClient")
        DataGridViewRecherche.DataSource = macnx.Executer_Requete_Select("SELECT idClient as NumeroClient, nomClient,PrenomClient,AdresseClient, CodePostalClient, VilleClient,TelPortClient,TelFixeClient,EmailClient,RenseignementCompAdresse  FROM Client Where IdClient like '" & idcli & "' ", "modifClient")
        DataGridViewRecherche.AutoResizeColumns()
        macnx.viderVue("modifClient2")
        donnees = macnx.Executer_Requete_Select("SELECT idClient as NumeroClient, nomClient,PrenomClient,AdresseClient, CodePostalClient, VilleClient,TelPortClient,TelFixeClient,EmailClient,RenseignementCompAdresse  FROM Client Where IdClient like '" & idcli & "' ", "modifClient2")
        nbchamps = donnees.Rows.Count - 1
        Rafraichir()
        macnx.Se_Deconnecter_MySqlServer()
        TextBoxNom.Clear()
        TextBoxId.Clear()
        TextBoxPrenom.Clear()
    End Sub


   
    Private Sub BtnFermer_Click(sender As System.Object, e As System.EventArgs) Handles BtnFermer.Click
        Close()
    End Sub
#Region "Fonction"
    Public Function RemplaceApostrophesSlash(ByRef unechaine As String) As String
        Dim ch As String
        ch = unechaine.Replace("'", "/")
        Return ch
    End Function


    Public Function RemplaceSlashApostrophes(ByRef unechaine As String) As String
        Dim ch As String
        ch = unechaine.Replace("/", "'")
        Return ch
    End Function

    Public Function RemplaceSlashApostrophesDataTable(ByRef lesdonnees As DataTable)
        Dim nblignes As Integer
        Dim nbcolonnes As Integer
        Dim test As String = ""
        nblignes = lesdonnees.Rows.Count
        nbcolonnes = lesdonnees.Columns.Count
        Dim i, j As Integer
        For i = 0 To nblignes - 1
            For j = 0 To nbcolonnes - 1
                'Attention le replace ne s'applique qu'aux type String : utilisation de la classe de base Object :
                If Object.ReferenceEquals(lesdonnees.Rows(i).Item(j).GetType(), test.GetType()) Then
                    lesdonnees.Rows(i).Item(j) = RemplaceSlashApostrophes(lesdonnees.Rows(i).Item(j))
                End If
            Next
        Next
        Return lesdonnees
    End Function
#End Region

End Class